﻿using System;
using System.Collections.Generic;
using System.Web.Caching;
using System.Text;
using HZ.Common;
using System.Data;
using System.Data.SqlClient;
using HZ.Supervise.Model;

namespace HZ.Supervise.DAL
{
	/// <summary>
	/// 地区数据访问逻辑
	/// </summary>
	public class DAL_Area
	{

		/// <summary>
		/// 条件地区查询
		/// </summary>
		/// <param name="where">查询用实体</param>
		/// <param name="currentPage">当前页</param>
		/// <param name="pageSize">每页显示记录数</param>
		/// <param name="totalRecord">（输出参数）总记录数目</param>
		/// <returns></returns>
		public static IList<Area> GetCompanyByWhere(HZ.Supervise.Model.Area query, int currentPage, int pageSize, out int totalRecord)
		{
			string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.Area>(query, false);

			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM TB_Area  WHERE 1=1 {0} ", where), pageSize);
			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;

			DataTable dt = pdes.GetSpecailPage();
			return DAL_Helper.CommonFillList<HZ.Supervise.Model.Area>(dt);
		}

		public static IList<Area> GetSonAreas(string areaid, int currentPage, int pageSize, out int totalRecord)
		{
			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM dbo.TB_Area WHERE FatherAreaID = '{0}' order by AreaID", areaid), pageSize);

			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;

			DataTable dt = pdes.GetSpecailPage();
			return DAL_Helper.CommonFillList<HZ.Supervise.Model.Area>(dt);
		}

		public static IList<Area> GetAreasByAreaID(string areaid)
		{
			areaid = "'" + areaid.Replace("'", "").Replace(",", "','") + "'";
			DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format("SELECT * FROM TB_Area WHERE AreaID IN ({0})", areaid));

			return DAL_Helper.CommonFillList<Model.Area>(dt);
		}
		public static IList<Area> GetSonAreas(string areaid)
		{
			string sql = string.Format("SELECT * FROM dbo.TB_Area WHERE FatherAreaID = '{0}'", areaid);


			DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

			return DAL_Helper.CommonFillList<Model.Area>(dt);
		}

		public static string GetAreaNamesByID(string areaid)
		{
			areaid = "'" + areaid.Replace("'", "").Replace(",", "','") + "'";


			DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format("SELECT AreaName FROM TB_Area WHERE AreaID IN ({0})", areaid));

			StringBuilder sb = new StringBuilder();
			bool first = true;
			foreach (DataRow item in dt.Rows)
			{
				if (first)
				{
					sb.Append(item[0].ToString());
					first = false;
				}
				else
				{
					sb.Append(item[0].ToString());
				}
			}
			return sb.ToString();
		}

		/// <summary>
		/// 获取区域之星
		/// </summary>
		/// <returns></returns>
		public static DataSet GetProcureAreaMax(string newsqlwhere, string oldsqlwhere, string orderwhere, string sqlArea)
		{
			StringBuilder sqldataNew = new StringBuilder();

			sqldataNew.AppendFormat(" select * from (");
			sqldataNew.AppendFormat(" select RANK () OVER(order by {0}) as id,", orderwhere);
			sqldataNew.AppendFormat(" A.AreaName,PurchaseAmount,DistributeAmount,DistributeRate,WarehouseAmount,WareHouseRate from (");
			sqldataNew.AppendFormat(" select A.AreaName,SUM(PurchaseAmount) AS PurchaseAmount,SUM(DistributeAmount) as DistributeAmount,SUM(WarehouseAmount) as WarehouseAmount,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate from CACHE_STAT A inner join TB_Area B on A.AreaId=B.AreaId ");



			if (sqlArea.IndexOf("F") != -1)
			{
				sqldataNew.AppendFormat(" where 1=1 AND FatherAreaID like'{0}%' ", sqlArea.Replace("F", ""));
				sqldataNew.AppendFormat(" {0} ", newsqlwhere);

				sqldataNew.AppendFormat(" group by A.AreaID,A.AreaName) A) B inner join TB_Area A on B.AreaName=A.AreaName where 1=1 and A.FatherAreaID like'{0}%'", sqlArea.Replace("F", ""));
			}

			if (sqlArea.IndexOf("=") != -1)
			{
				string tempAreaId = sqlArea.Replace("=", "");
				sqldataNew.AppendFormat(" where 1=1 AND A.AreaId LIKE '{0}%' ", tempAreaId.Replace("=", ""));
				sqldataNew.AppendFormat(" {0} ", newsqlwhere);

				sqldataNew.AppendFormat(" group by A.AreaID,A.AreaName) A) B inner join TB_Area A on B.AreaName=A.AreaName where 1=1 and A.AreaId like'{0}%'", tempAreaId);
			}

			if (sqlArea.IndexOf("L") != -1)
			{
				sqldataNew.AppendFormat(" where 1=1 AND A.AreaId LIKE '{0}%' ", sqlArea.Replace("L", ""));
				sqldataNew.AppendFormat(" {0} ", newsqlwhere);

				sqldataNew.AppendFormat(" group by A.AreaID,A.AreaName) A) B inner join TB_Area A on B.AreaName=A.AreaName where 1=1 and A.AreaId LIKE '{0}%'", sqlArea.Replace("L", ""));
			}


			sqldataNew.AppendFormat(" order by {0} ", orderwhere);

			DataTable dtNewdata = new DataTable();

			dtNewdata = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqldataNew.ToString());


			StringBuilder sqldataOld = new StringBuilder();

			sqldataOld.AppendFormat(" select * from (");
			sqldataOld.AppendFormat(" select RANK () OVER(order by {0}) as id,", orderwhere);
			sqldataOld.AppendFormat(" A.AreaName,PurchaseAmount,DistributeAmount,DistributeRate,WarehouseAmount,WareHouseRate from (");

			sqldataOld.AppendFormat(" select A.AreaName, SUM(PurchaseAmount) AS PurchaseAmount,SUM(DistributeAmount) as DistributeAmount,SUM(WarehouseAmount) as WarehouseAmount,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate,case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate from CACHE_STAT A inner join TB_Area B on A.AreaId=B.AreaId ");

			if (sqlArea.IndexOf("F") != -1)
			{
				sqldataOld.AppendFormat(" where 1=1 AND FatherAreaID like'{0}%' ", sqlArea.Replace("F", ""));
				sqldataOld.AppendFormat(" {0} ", oldsqlwhere);

				sqldataOld.AppendFormat(" group by A.AreaID,A.AreaName) A) B inner join TB_Area A on B.AreaName=A.AreaName where 1=1 and A.FatherAreaID like'{0}%'", sqlArea.Replace("F", ""));
			}

			if (sqlArea.IndexOf("=") != -1)
			{
				string tempAreaId = sqlArea.Replace("=", "");
				sqldataOld.AppendFormat(" where 1=1 AND A.AreaId LIKE '{0}%' ", tempAreaId.Replace("=", ""));
				sqldataOld.AppendFormat(" {0} ", oldsqlwhere);

				sqldataOld.AppendFormat(" group by A.AreaID,A.AreaName) A) B inner join TB_Area A on B.AreaName=A.AreaName where 1=1 and A.AreaId like'{0}%'", tempAreaId);
			}

			if (sqlArea.IndexOf("L") != -1)
			{
				sqldataOld.AppendFormat(" where 1=1 AND A.AreaId LIKE '{0}%' ", sqlArea.Replace("L", ""));
				sqldataOld.AppendFormat(" {0} ", oldsqlwhere);

				sqldataOld.AppendFormat(" group by A.AreaID,A.AreaName) A) B inner join TB_Area A on B.AreaName=A.AreaName where 1=1 and A.AreaId LIKE '{0}%'", sqlArea.Replace("L", ""));
			}


			sqldataOld.AppendFormat(" order by {0} ", orderwhere);


			DataTable dtOlddata = new DataTable();

			dtOlddata = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqldataOld.ToString());


			DataSet ds = new DataSet();

			ds.Tables.Add(dtNewdata);
			ds.Tables.Add(dtOlddata);

			return ds;

		}
		/// <summary>
		/// 曹鹏
		/// 2011年5月17日9:10:51
		/// 地区排名（县区）
		/// </summary>
		/// <param name="orderOfRank">排名的条件</param>
		/// <param name="orderBy">返回时排序条件</param>
		/// <param name="wherePm">本月查询条件</param>
		/// <param name="wherePms">上月的查询条件</param>
		/// <param name="currentpage">当前页</param>
		/// <param name="pagesize">每页数据条数</param>
		/// <param name="totalrecord">总页数</param>
		/// <returns>数据源</returns>
		public static DataTable GetProcureAreaMax1(string orderOfRank, string orderBy, string wherePm, string wherePms, string sortSource, int currentpage, int pagesize, out int totalrecord)
		{

			string sql = "";

            //by张雄
            //if (sortSource == "2")
            //{

            //    sql = string.Format(" select pm,pms,A.AreaName,PurchaseAmountTotal,DistributeAmountTotal,WarehouseAmountTotal,WareHouseRate,DistributeRate,A.SortSorce from (( select RANK () OVER(order by  {0}  DESC ) as pm, AreaName,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate,2 as SortSorce from CACHE_STAT where 1=1 {1}  group by AreaID,AreaName) A left join (select RANK () OVER(order by  {0}  DESC ) as pms, AreaName,2 as SortSorce from CACHE_STAT where 1=1 {2}  group by AreaID,AreaName ) B on A.AreaName=B.AreaName)  order by  {3}", orderOfRank, wherePm, wherePms, orderBy);

            //}
            //else
            //{
            sql = string.Format(" select pm,pms,A.AreaName,PurchaseAmountTotal,DistributeAmountTotal,WarehouseAmountTotal,WareHouseRate,DistributeRate from (( select RANK () OVER(order by  {0}  DESC ) as pm, AreaName,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate from CACHE_STAT where 1=1 {1}  group by AreaID,AreaName) A left join (select RANK () OVER(order by  {0}  DESC ) as pms, AreaName,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouse, case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as Distribute from CACHE_STAT where 1=1 {2}  group by AreaID,AreaName ) B on A.AreaName=B.AreaName )  order by  {3}", orderOfRank, wherePm, wherePms, orderBy);

            //}
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();

		}
		/// <summary>
		/// 曹鹏
		/// 2011年5月17日9:10:51
		/// 地区排名(市)
		/// </summary>
		/// <param name="orderOfRank">排名的条件</param>
		/// <param name="orderBy">返回时排序条件</param>
		/// <param name="wherePm">本月查询条件</param>
		/// <param name="wherePms">上月的查询条件</param>
		/// <param name="currentpage">当前页</param>
		/// <param name="pagesize">每页数据条数</param>
		/// <param name="totalrecord">总页数</param>
		/// <returns>数据源</returns>
		public static DataTable GetProcureAreaMax2(string strgroupBy, string orderOfRank, string orderBy, string wherePm, string wherePms, string sortSource, int currentpage, int pagesize, out int totalrecord)
		{
            string str = "AND AreaID LIKE '22%'";
             string sql = string.Format(@" 
select * from ( select A.areaid1,pm,pms,PurchaseAmountTotal,DistributeAmountTotal,WarehouseAmountTotal,WareHouseRate,DistributeRate,A.SortSorce from (( 
select RANK () OVER(order by  {0}  DESC ) as pm,{4} as areaid1,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,
SUM(WarehouseAmount) as WarehouseAmountTotal,case when SUM(WarehouseAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 
when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 
else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouseRate, 
case when SUM(DistributeAmount) = 0 then 0 when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 
else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as DistributeRate,SortSorce from CACHE_STAT where 1=1  {1}  {5}
group by {4},SortSorce) A left join (select RANK () OVER(order by  {0}  DESC ) as pms,{4} as areaid,case when SUM(WarehouseAmount) = 0 then 0 
when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount))>1 then 1 
else CONVERT(decimal(18,3),CONVERT(float, SUM(WarehouseAmount)) / SUM(PurchaseAmount)) end as WareHouse, case when SUM(DistributeAmount) = 0 then 0 
when SUM(PurchaseAmount)=0 then 0 when CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount))>1 then 1 
else CONVERT(decimal(18,3),CONVERT(float, SUM(DistributeAmount)) / SUM(PurchaseAmount)) end as Distribute,SortSorce from CACHE_STAT 
where 1=1  {2} {5}  group by {4},SortSorce ) B on A.areaid1=B.areaid  and A.SortSorce=B.SortSorce)) as A inner join TB_Area B on A.areaid1+'00'=B.AreaID  order by  {3}
", orderOfRank, wherePm, wherePms, orderBy, strgroupBy,str);


			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;

			totalrecord = des.TotalCount;

			return des.GetSpecailPage();

		}
		#region 董辉 2011-4-24 23:41 树形菜单生成方法

		/// <summary>
		/// 返回保存在cache中的所有安徽省的市，县等数据
		/// </summary>
		private void GetDataCache(string areaid)
		{
			DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format("select * from TB_Area where 1=1 {0} and AreaName <> '省直属' and AreaName<>'市辖区'", areaid));

			System.Web.HttpContext.Current.Cache.Insert("dt", dt, null, DateTime.Now.AddDays(1), TimeSpan.Zero);
		}

		/// <summary>
		/// 得到根节点数据
		/// </summary>
		/// <returns></returns>
		private string GetRoot()
		{
			StringBuilder json = new StringBuilder();

			json.Append("[");

			DataTable dt = System.Web.HttpContext.Current.Cache.Get("dt") as DataTable;

			dt.TableName = "area";

			DataView view = new DataView();

			view.Table = dt;

            for (int i = 0; i < dt.Rows.Count; i++)//add by 曹鹏2011年5月17日16:49:18 如果是市，就根据父节点“510000”过滤，如果是县就不用过滤
			{

                if (dt.Rows[i]["FatherAreaID"].ToString() == "510000")
				{

                    view.RowFilter = "FatherAreaID = '510000'";

					break;

				}

			}

			if (view.Count > 0)
			{
				int count = 0;

				foreach (DataRowView row in view)
				{
					json.Append("{");

					json.Append("\"id\":\"" + row["AreaID"].ToString() + "\",\"Pid\":\"" + row["FatherAreaID"].ToString() + "\",\"name\":\"" + row["AreaName"].ToString() + "\",\"icon\":\"../lib/zStyle/img/moveArrow.png\"");

					GetChildren(row["AreaID"].ToString(), json);

					if (count == (view.Count - 1))
					{
						json.Append("}");
					}
					else
					{
						json.Append("},");
					}

					count++;
				}
			}

			json.Append("]");

			return json.ToString();
		}

		/// <summary>
		/// 得到子数据
		/// </summary>
		/// <param name="RootAreaID"></param>
		/// <returns></returns>
		private void GetChildren(string RootAreaID, StringBuilder json)
		{
			DataTable dt = System.Web.HttpContext.Current.Cache.Get("dt") as DataTable;

			dt.TableName = "area";

			DataView view = new DataView();

			view.Table = dt;

			view.RowFilter = "FatherAreaID = '" + RootAreaID + "'";

			int count = 0;

			if (view.Count > 0)
			{
				json.Append(",\"nodes\":[");

				foreach (DataRowView row in view)
				{
					json.Append("{\"id\":\"" + row["AreaID"].ToString() + "\",\"Pid\":\"" + row["FatherAreaID"].ToString() + "\",\"name\":\"" + row["AreaName"].ToString() + "\",\"icon\":\"../lib/zStyle/img/sim/1_open.png\"");

					if (count == (view.Count - 1))
					{
						json.Append("}");
					}
					else
					{
						json.Append("},");
					}

					count++;
				}

				json.Append("]");
			}
		}

		/// <summary>
		/// 获取JSON数据
		/// </summary>
		/// <returns></returns>
		public string GetJSON(string areaid)
		{

			System.Web.HttpContext.Current.Cache.Remove("dt");

			if (System.Web.HttpContext.Current.Cache.Get("dt") == null)
			{
				GetDataCache(areaid);

				return GetRoot();
			}
			else
			{
				return GetRoot();
			}
		}

		#endregion

		/// <summary>
		/// 曹鹏
		/// 2011-4-27 8:55:44
		/// 根据地区县的名称得到该地区的去名称（某某市某某县）
		/// </summary>
		public string GetCityNameByCountryName(string contryName)
		{

			string sql = String.Format("select AreaName from TB_Area where AreaID = (select FatherAreaID from TB_Area where AreaName='{0}')", contryName);

			return Convert.ToString(HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql));

		}
		/// <summary>
		/// 曹鹏
		/// 2011年5月24日17:38:14
		/// 取得该地区下面的子地区，如果该地区没有子地区，那么就显示它本身
		/// </summary>
		/// <param name="flag"></param>
		/// <param name="areaid"></param>
		/// <param name="currentPage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalRecord"></param>
		/// <returns></returns>
		public static IList<Area> GetAreas(bool flag, string areaid, int currentPage, int pagesize, out int totalRecord)
		{

			string sql = "";

			if (flag)
			{

				sql = string.Format("SELECT * FROM dbo.TB_Area WHERE AreaID = '{0}'", areaid);

			}
			else
			{

				sql = string.Format("SELECT * FROM dbo.TB_Area WHERE FatherAreaID = '{0}' order by AreaID", areaid);

			}


			HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);

			pdes.CurrentPage = currentPage;
			totalRecord = pdes.TotalCount;

			DataTable dt = pdes.GetSpecailPage();
			return DAL_Helper.CommonFillList<HZ.Supervise.Model.Area>(dt);
		}
	}
}
